﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Frunit.Service
{
    /// <summary>
    /// 主要水果生产情况季度统计表通用操作类
    /// </summary>
    public class FruProSeasons
    {
        /// <summary>
        /// 添加
        /// </summary>
        public int AddInfo(List<FruProSeasonEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_FruProSeason";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "Variety,";
            strSql += "ZyArea,";
            strSql += "YshArea,";
            strSql += "YshYield,";
            strSql += "AvgPrice,";
            strSql += "ParentCode,";
            strSql += "ParentName,";
            strSql += "VarietyCode,";
            strSql += "SortNum,";
            strSql += "AddArea)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@Variety,";
            strSql += "@ZyArea,";
            strSql += "@YshArea,";
            strSql += "@YshYield,";
            strSql += "@AvgPrice,";
            strSql += "@ParentCode,";
            strSql += "@ParentName,";
            strSql += "@VarietyCode,";
            strSql += "@SortNum,";
            strSql += "@AddArea)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        list[i].Id = CustomFun.GetGuid();
                    }
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.SuccessResult;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取T_FruProSeason对象信息失败", ex);
                return DefaultKey.ErrorResult;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        public ResResult<int> UpdateInfo(List<FruProSeasonEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_FruProSeason set ";
            strSql += "Variety = @Variety,";
            strSql += "ZyArea = @ZyArea,";
            strSql += "YshArea = @YshArea,";
            strSql += "YshYield = @YshYield,";
            strSql += "AvgPrice = @AvgPrice,";
            strSql += "AddArea = @AddArea,";
            strSql += "Remark = @Remark";
            strSql += " where Id = @Id";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var trans = dbCon.BeginTransaction();
                    int result = dbCon.Execute(strSql, list, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    trans.Commit();

                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新主要水果生产情况季度统计表对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        public virtual ResResult<int> DeleteInfo(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_FruProSeason where Id = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除主要水果生产情况季度统计表对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        public virtual ResResult<int> DeleteBatch(string[] ids)
        {
            ResResult<int> obj = new ResResult<int>();
            string strIds = string.Join(",", ids.Select(x => x.ToString()).ToArray());
            string strSql = @"delete from dbo.T_FruProSeason where Id in @ids";
            var dynamicSqlParam = new DynamicParameters();
            dynamicSqlParam.Add("ids", ids.ToArray(), null, null, null);
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, dynamicSqlParam);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取数据字典表批量删除主要水果生产情况季度统计表表失败", ex);
                obj.message = ex.ToString().Trim();
                obj.status = DefaultKey.ErrorResult;
                return obj;
            }
        }
        /// <summary>
        /// 获取指定对象
        /// </summary>
        public ResResult<List<FruProSeasonEntity>> GetModel(string reportId)
        {
            ResResult<List<FruProSeasonEntity>> obj = new ResResult<List<FruProSeasonEntity>>();
            if (CheckRepCount(reportId))
            {               
                List<FruProSeasonEntity> templist = GetTabContent(reportId);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表T_FruProSeason对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            else
            {
                try
                {
                    string strSql = @"select * from dbo.T_FruProSeason where ReportId = @ReportId order by SortNum";
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        List<FruProSeasonEntity> templist = dbCon.Query<FruProSeasonEntity>(strSql, new { ReportId = reportId }).ToList();
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放

                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            return obj;
        }
        /// <summary>
        /// 用于获取主要水果生产情况季度统计表的种类
        /// </summary>
        /// <returns></returns>
        public List<FruProSeasonEntity> GetTabContent(string reportId)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruProSeasonEntity> list = new List<FruProSeasonEntity>();
            string pathName = HttpRuntime.AppDomainAppPath.ToString() + @"Excel\FruProSeason.xls";
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            workbook = new HSSFWorkbook(fs);
            sheet = workbook.GetSheetAt(0);
            int startRow = 3;
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                int sortNum = 0;
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    FruProSeasonEntity entity = new FruProSeasonEntity();
                    entity.ReportId = reportId;
                    entity.ParentCode = row.GetCell(0).ToString();
                    entity.ParentName = row.GetCell(1).ToString();
                    entity.VarietyCode = row.GetCell(2).ToString();
                    entity.Variety = row.GetCell(3).ToString();
                    entity.SortNum = sortNum++;
                    list.Add(entity);
                }
            }
            return list;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_FruProSeason where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取主要水果生产情况季度统计表T_FruProSeason对象信息失败", ex);
                return false;
            }
        }
        /// <summary>
        /// 获取全部列表
        /// </summary>
        /// <returns></returns>
        public virtual ResResult<List<FruProSeasonEntity>> GetList()
        {
            ResResult<List<FruProSeasonEntity>> obj = new ResResult<List<FruProSeasonEntity>>();
            string strSql = @"select * from dbo.T_FruProSeason";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<FruProSeasonEntity>(strSql).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新主要水果生产情况季度统计表对象信息失败", ex);
                return obj;
            }
        }
    }
}